/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import model.ConnectionFactory;
import model.beans.Evento;
import model.JavaMailApp;

/**
 *
 * @author Juliane Chaerki
 */
public class EventoDAO {

    private Connection con;
    private PreparedStatement stmtInserir;
    private PreparedStatement stmtBuscar;
    private PreparedStatement stmtDeletar;
    private PreparedStatement stmtAlterar;
    private PreparedStatement stmtBuscaId;
    private PreparedStatement stmtEmail;
    
     public EventoDAO() throws Exception {
        con = ConnectionFactory.getConnection();
    }

    public EventoDAO(Connection con) throws Exception {
        this.con = con;
    }
    
    public void inserirEvento(Evento evento){
        try{
            stmtInserir = con.prepareStatement("INSERT INTO evento(nome, data, mensagem, tipo) VALUES(?,?,?,?)");
            stmtInserir.setString(1, evento.getNome());
            stmtInserir.setDate(2, new java.sql.Date(evento.getData().getTime()));
            stmtInserir.setString(3, evento.getMensagem());
            stmtInserir.setInt(4, evento.getTipo());

            stmtInserir.executeUpdate();
        }catch(Exception ex){
            ex.getMessage();
        }
    }
    
    public List<Evento> buscarEvento(String a){
        try{
            String str = "%" + a + "%";
            stmtBuscar = con.prepareStatement("SELECT e.idEvento, e.nome, e.data, e.mensagem, t.nome FROM evento e JOIN tipoconsulta t WHERE e.tipo = t.idTpCons AND e.nome like ?");

            stmtBuscar.setString(1, str);

            ResultSet rs = stmtBuscar.executeQuery();
            List<Evento> listaEvento = new ArrayList();
            while(rs.next()){
                Evento evento = new Evento();
                evento.setId(rs.getInt("e.idEvento"));
                evento.setNome(rs.getString("e.nome"));
                evento.setData(rs.getDate("e.data"));
                evento.setMensagem(rs.getString("e.mensagem"));
                evento.setNometipo(rs.getString("t.nome"));
                listaEvento.add(evento);
            }
            return listaEvento; 
        }catch(Exception ex){
            return null;
        }
    }
    
    public void deletar(int id){
        try{
            stmtDeletar = con.prepareStatement("DELETE FROM evento WHERE idEvento = ?");
            stmtDeletar.setInt(1, id);
            stmtDeletar.executeUpdate();
        }catch(Exception ex){
            ex.getMessage();
        }
    }
    
    public void alterar(Evento e){
        try{
            stmtAlterar = con.prepareStatement("UPDATE evento set nome = ?, data = ?, mensagem = ?, tipo = ? WHERE idEvento = ?");
            stmtAlterar.setString(1, e.getNome());
            stmtAlterar.setDate(2, new java.sql.Date(e.getData().getTime()));
            stmtAlterar.setString(3, e.getMensagem());
            stmtAlterar.setInt(4, e.getTipo());
            stmtAlterar.setInt(5, e.getId());
            stmtAlterar.executeUpdate();
        }catch(Exception ex){
            ex.getMessage();
        }
    }
    
    public Evento buscaId(int id){
        try{
            stmtBuscaId = con.prepareStatement("SELECT * FROM evento WHERE idEvento = ?");
            stmtBuscaId.setInt(1, id);
            ResultSet rs = stmtBuscaId.executeQuery();
            Evento e = new Evento();
            if(rs.next()){
                e.setId(rs.getInt("idEvento"));
                e.setNome(rs.getString("nome"));
                e.setMensagem(rs.getString("mensagem"));
                e.setTipo(rs.getInt("tipo"));
                e.setData(rs.getDate("data"));
            }
            return e;
        }catch(Exception ex){
            return null;
        }
    }
    
    public String email(int tipo){
        try{
            stmtEmail= con.prepareStatement("SELECT cl.email FROM cliente cl, consulta co, evento e, tipoconsulta tc WHERE co.idCliente = cl.idCliente AND co.idTpCons = ? group by cl.email");
            stmtEmail.setInt(1, tipo);
            ResultSet rs = stmtEmail.executeQuery();
            StringBuilder email = new StringBuilder();
            while(rs.next()){
                email.append(rs.getString("cl.email"));
                email.append(", ");
            }
            return email.toString();
        }catch(Exception ex){
            return null;
        }
    }
  
    @Override
    public void finalize() throws Throwable{
        super.finalize();
        stmtInserir.close();
        stmtBuscar.close();
        stmtDeletar.close();
        con.close();
    }
}
